Working with Web-SQL Database & HTML5

Hi everyone in this article, I’m explaining about HTML5 and Web SQL Database.

Description:

Web SQL is very interesting feature, even though it isn't part of the HTML 5 specification. But it is a separate specification and it can still help for developing web applications. Web SQL is used to manipulate a client-side database. Since I am saying that it is good to use, there is a disclaimer for its use; it is risky because it stores data at the client side, not on the server side. So always remember, don't store information sensitive to the server inside it.

Note:

A Web SQL database only works in the latest versions of Safari, Google Chrome and Opera browsers.

Core Methods of Web SQL:

The following are the 3 core methods of Web SQL that I will cover in this article:

openDatabase
transaction
executeSql

Create and open Database:

Using the openDatabase method you can create an object for the database. If the database doesn't exist then it will be created and then an object for that database will be created. You also don't need to worry about closing the connection with the database.

To create and open the database you need to use the following syntax.

var dbobj = openDatabase('Database-Name','Version-Number','Text-Description','Database-Size','Creation-Callback');

Example:

The following example describes how to create a database or its object.

1. First, create a button in your HTML 5 page as in the following:

<divclass="container"style="width: 40%; margin: 100pxauto;">
 
    <inputtype="button"id="btnCreateDB"value="Create Database"class="btn btn-primary"/>
</div>

2. Now create a JavaScript function to create the database as in the following:

function CreateDB() {
        var dbObj = openDatabase('Mindstick', 1.0, 'My First Web-SQL Example', 2 * 1024 * 1024, OnSuccessCreate());
    }
    function OnSuccessCreate() {
        alert('Database Created Sucessfully');
    }

3. Now bind this JavaScript function to the onclick event of the btnCreateDB button. The complete code is given below:

<script>
 
 
    function CreateDB() {
        var dbObj = openDatabase('Mindstick', 1.0, 'My First Web-SQL Example', 2 * 1024 * 1024, OnSuccessCreate());
    }
    function OnSuccessCreate() {
        alert('Database Created Sucessfully');
    }
 
</script>
 
 
<divclass="container"style="width: 40%; margin: 100pxauto;">
 
    <inputtype="button"id="btnCreateDB"value="Create Database"onclick="CreateDB()"class="btn btn-primary"/>
</div>

4. Now open this file, I am opening it in Google Chrome. By default the output will be:

5. Now press the F12 function key and open the Google Chrome developer tool and open the Resource tab where you will get a Web SQL database.

6. Now click on the Create Database button and you will get the following output.

And inside the developer tool you will get the database.

Since you saw how to create and open the database in Web SQL, so by using the openDatabase function we can create a database in Web SQL and open the database. There are 5 parameters that are accepted by this openDatabase function that are:

Database name: This argument provides the name of the database that is mandatory to be provided, otherwise you will get an exception.

Version number: Version number is also required; some database may be in version 2.0 and may be in 1.0 so if you know the version number of the database then only you can open it.

Text description: This argument describes the database and provides information about the database.

Size of database: This argument decides the size of the database.

Creation callback: This argument is optional, if you do not provide any value then the database will also be created but if you want to perform some action after creation of the database then you can use this, so if the database is created successfully then this work will be done.

Transactions:

After opening our database we can create transactions. This provides the rollback and commit facility. This means inside the transaction we can fire more than one query. If a transaction fails at any point of time or a query has an error then it will be rolled back including all the queries and if all the queries successfully executed then the transaction will be committed.

A transaction is the same as a function that contains more than one query

statement.

Example:

function CreateDB() { 
        var dbObj = openDatabase('Mindstick', 1.0, 'My First Web-SQL Example', 2 * 1024 * 1024); 
    } 
    dbObj.transaction(function (tx) { 
        //Code of the transaction 
        //will goes here 
    });

executeSql:

This method performs a very important role for the Web SQL database. This method is used to execute read and write statements which include SQL injection projection and provides a call back method to process the result of any queries. Once if we have a transaction object then we can call the executeSql method.

Example:

The following example also explains how to create a table in Web SQL.

<script>
 
    function CreateDB() {
        var dbObj = openDatabase('Mindstick', 1.0, 'My First Web-SQL Example', 2 * 1024 * 1024);
        dbObj.transaction(function (tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS Student_Record (id unique, Name, Location)');
        });
    }
 
</script>
 
 
<divclass="container"style="width: 40%; margin: 100pxauto;">
    <inputtype="button"id="Create_DB_n_Table"value="Create Database & Table"onclick="CreateDB()"class="btn btn-primary"/>
</div>

Output of the preceding Example:

1. Whe the page is loaded

2. If you then open the developer tool of Google Chrome then you will get the

following output:

3. After clicking on the button:

How to inset the data into Web SQL table:

The following example will explain how to insert the data into the database.

<script> 
    var dbObj = openDatabase('Mindstick', 1.0, 'My First Web-SQL Example', 2 * 1024 * 1024); 
    dbObj.transaction(function (tx) { 
        tx.executeSql('CREATE TABLE IF NOT EXISTS Student_Record (id unique, Name, Location)'); 
    }); 

    function Insert() { 
        var id = document.getElementById("ID").value; 
        var name = document.getElementById("Name").value; 
        var location = document.getElementById("Location").value; 
        dbObj.transaction(function (tx) { 
            tx.executeSql('insert into Student_Record(id, Name, Location) values(' + id + ',"' + name + '","' + location + '")'); 
        }); 
    } 
</script> 


<div class="container" style="width: 40%; margin: 100px auto;"> 

    <div class="row well"> 
        <div class="row"> 
            <div class="col-md-4">User ID:</div> 
            <div class="col-md-8"> 
                <input type="text" id="ID" style="width: 100%;" /> 
            </div> 
        </div> 
        <div class="row"> 
            <br /> 
            <div class="col-md-4">Name:</div> 
            <div class="col-md-8"> 
                <input type="text" id="Name" style="width: 100%;" /> 
            </div> 
        </div> 
        <div class="row"> 
            <br /> 
            <div class="col-md-4">Location:</div> 
            <div class="col-md-8"> 
                <input type="text" id="Location" style="width: 100%;" /> 
            </div> 
        </div> 
        <div class="row"> 
            <br /> 
            <div class="col-md-12 text-right"> 
                <button id="insert" class="btn btn-primary" onclick="Insert()">Insert</button> 
            </div> 
        </div> 
    </div> 

</div>

In the preceding example I have created one form and created 3 Textboxes and

one button to get the value and submit the value.

1. When the page is loaded then:

2. If you look at the developers tool then you will get output like this:

3. When you fill in some data into the TextBoxes and submit it then:

How to read the data from the web SQL:

The following example will explain how to read the data from the Web SQL.

<script> 
    var dbObj = openDatabase('Mindstick', 1.0, 'My First Web-SQL Example', 2 * 1024 * 1024); 
    dbObj.transaction(function (tx) { 
        tx.executeSql('CREATE TABLE IF NOT EXISTS Student_Record (id unique, Name, Location)'); 
    }); 

    function Insert() { 
        var id = document.getElementById("ID").value; 
        var name = document.getElementById("Name").value; 
        var location = document.getElementById("Location").value; 
        dbObj.transaction(function (tx) { 
            tx.executeSql('insert into Student_Record(id, Name, Location) values(' + id + ',"' + name + '","' + location + '")'); 
        }); 
    } 

    dbObj.transaction(function (tx) { 
        tx.executeSql('SELECT * FROM Student_Record', [], function (tx, results) { 
            var len = results.rows.length, i; 
            var str = ''; 
            for (i = 0; i < len; i++) { 
                str += "<tr>"; 
                str += "<td class='text-success'>" + results.rows.item(i).id + "</td>"; 
                str += "<td class='text-success'>" + results.rows.item(i).Name + "</td>"; 
                str += "<td class='text-success'>" + results.rows.item(i).Location + "</td>"; 
                str += "</tr>"; 
                document.getElementById("tblGrid").innerHTML += str; 
                str = ''; 
            } 
        }, null); 
    }); 

</script> 


<div class="container" style="width: 40%; margin: 100px auto;"> 

    <div class="row well"> 
        <div class="row"> 
            <div class="col-md-4">User ID:</div> 
            <div class="col-md-8"> 
                <input type="text" id="ID" style="width: 100%;" /> 
            </div> 
        </div> 
        <div class="row"> 
            <br /> 
            <div class="col-md-4">Name:</div> 
            <div class="col-md-8"> 
                <input type="text" id="Name" style="width: 100%;" /> 
            </div> 
        </div> 
        <div class="row"> 
            <br /> 
            <div class="col-md-4">Location:</div> 
            <div class="col-md-8"> 
                <input type="text" id="Location" style="width: 100%;" /> 
            </div> 
        </div> 
        <div class="row"> 
            <br /> 
            <div class="col-md-12 text-right"> 
                <button id="insert" class="btn btn-primary" onclick="Insert()">Insert</button> 
            </div> 
        </div> 
    </div> 
    <br /> 

    <div class="col-md-12"> 
        <div class="table-responsive"> 
            <table id="tblGrid" class="table table-bordered table-striped"> 
                <thead> 
                    <tr> 
                        <th>ID</th> 
                        <th>Name</th> 
                        <th>Location</th> 
                    </tr> 
                </thead> 
                <tbody> 
                </tbody> 
            </table> 
        </div> 
    </div> 

</div>